﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using CourseManagerModels;
using CourseManagerDAL.Helper;

namespace CourseManagerDAL
{
    public class CourseService
    {
        #region  添加课程
        public int AddCourse(Course course)
		{
            //封装sql，存在sql注入的风险
            //string sql = $"insert into Course (CourseName,CourseContent,ClassHour,Credit,CategoryId,TeacherId)" +
            //    $" values ({course.CourseName},{course.CourseContent},{course.ClassHour},{course.Credit},{course.CategoryId},{course.TeacherId})";
            //int affects = SQLHelper.Update(sql);

            string sql = " insert into Course(CourseName, CourseContent, ClassHour, Credit, CategoryId, TeacherId)";
            sql+= " values(@CourseName, @CourseContent, @ClassHour, @Credit, @CategoryId, @TeacherId)";
            //封装SQL语句中的参数
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@CourseName",course.CourseName),
                new SqlParameter("@CourseContent",course.CourseContent),
                new SqlParameter("@ClassHour",course.ClassHour),
                new SqlParameter("@Credit",course.Credit),
                new SqlParameter("@CategoryId",course.CategoryId),
                new SqlParameter("@TeacherId",course.TeacherId),
            };
            return SQLHelper.Update(sql, param);


		}
		#endregion
		#region  查询课程
        public List<Course> QueryCourse(int categoryId,string courseName)
		{
            //定义sql语句
            string sql = "select CourseId,CourseName,CourseContent,ClassHour,Credit,CategoryId,TeacherName,Course.TeacherId from Course";
            sql += " inner join Teacher on Teacher.TeacherId=Course.TeacherId where ";

            //组合条件
            string whereSql = string.Empty;
			if (categoryId != -1)
			{
                whereSql += " and CategoryId=" + categoryId;
            }
            if (courseName != "") //这个地方没有必要检查null，因为我们通过文本框架文本传递的数据永远不可能为null
            {
                whereSql += $" and CourseName like '%{courseName}%'";
            }

            sql += whereSql.Substring(4);//把第一个and去掉后，组合

            //执行查询
            SqlDataReader reader = SQLHelper.GetReader(sql);

            //封装结果
            List<Course> list = new List<Course>();
			while (reader.Read())
			{
                list.Add(new Course
                {
                    CourseId = (int)reader["CourseId"],
                    CourseName = reader["CourseName"].ToString(),
                    CourseContent = reader["CourseContent"].ToString(),
                    ClassHour = (int)reader["ClassHour"],
                    Credit = (int)reader["Credit"],
                    CategoryId = (int)reader["CategoryId"],
                    TeacherId = (int)reader["TeacherId"],
                    TeacherName = reader["TeacherName"].ToString()
                });
			}
            reader.Close();
            return list;
        }

        #endregion
        #region 修改课程对象
        public int ModifyCourse(Course course)
        {
            //定义SQL语句
            string sql = $"update Course Set CourseName=@CourseName,CourseContent=@CourseContent,ClassHour=@ClassHour,Credit=@Credit,CategoryId=@CategoryId ";
            sql += " where CourseId=@CourseId";
            //封装参数
            SqlParameter[] param = new SqlParameter[]
              {
                    new SqlParameter("@CourseName",course.CourseName),
                    new SqlParameter("@CourseContent",course.CourseContent),
                    new SqlParameter("@ClassHour",course.ClassHour),
                    new SqlParameter("@Credit",course.Credit),
                    new SqlParameter("@CategoryId",course.CategoryId),
                    new SqlParameter("@CourseId",course.CourseId)
              };
            //提交保存
            return SQLHelper.Update(sql, param);
        }
		#endregion
		#region 删除课程对象
		public int DeleteCourse(Course course)
        {
            string sql = "delete from Course where CourseId=" + course.CourseId;
            return SQLHelper.Update(sql);
        }
        #endregion
    }
}

